﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;
using Bortosky.Google.Visualization;

namespace HospitalSystem
{
    public partial class ReportBalancePerChild : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                
                //อย่าลืมทำเช็คเงื่อนไข เมื่อ แปลง CaseNumber กับ CID มามั่วๆ
                string caseNumber = Request.QueryString["CaseNo"];
                string CID = Request.QueryString["IDStudent"];
                
                if (caseNumber == "" || CID == "")
                {
                    MessageBox("Error!!!!!!!");
                }
                else
                {
                    LoadChart(this.SortingDataTable(CID, caseNumber), "fundata");
                    LoadChart(this.PieChartData(CID, caseNumber), "piedata");
                }
            }
        }

        //LoadChart For GetJson To Show Graph(using Google Visualization)
        private void LoadChart(DataTable table, string jsName)
        {
            var gdt = new Bortosky.Google.Visualization.GoogleDataTable(table);
            using (var mem = new System.IO.MemoryStream())
            {
                gdt.WriteJson(mem);
                mem.Position = 0;
                using (var sr = new System.IO.StreamReader(mem))
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), jsName, string.Format("var {0} = {1};", jsName, sr.ReadToEnd()), true);
                    sr.Close();
                };
            }
        }

        protected DataTable PieChartData(string CID, string caseNumber)
        {
            DataTable tempPSNumber = new DataTable();
            DataTable temp = new DataTable();
            DataTable showTable = new DataTable();

            tempPSNumber.Columns.Add("PSNumber");
            temp.Columns.Add("ACTCode");
            temp.Columns.Add("ACTDESC");
            showTable.Columns.Add("Activity");
            showTable.Columns.Add("Money",typeof(System.Double));

            //double totalMoney = 0;

            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);
            scon.Open();
            SqlCommand cmd5 = new SqlCommand("select PSNumber from ServiceProvisionVsPerson where CID = @CID and CaseNo = @CaseNo", scon);
            cmd5.Parameters.AddWithValue("@CID", CID);
            cmd5.Parameters.AddWithValue("@CaseNo", caseNumber);
            SqlDataReader reader5 = cmd5.ExecuteReader();
            while (reader5.Read() != false)
            {
                tempPSNumber.Rows.Add(reader5["PSNumber"]);
            }
            reader5.Close();
            for (int i = 0; i < tempPSNumber.Rows.Count; i++)
            {
                //ตอนนี้ที่แสดงยังเป็น ActivityCode อยู่ต้องแก้
                SqlCommand cmd6 = new SqlCommand("select ServiceProvisionDetails.ACTCode from ServiceProvision inner join ServiceProvisionDetails on ServiceProvision.PSNumber = ServiceProvisionDetails.PSNumber where ServiceProvision.PSNumber = @PSNumber", scon);
                cmd6.Parameters.AddWithValue("@PSNumber", tempPSNumber.Rows[i]["PSNumber"]);
                SqlDataReader reader6 = cmd6.ExecuteReader();
                while (reader6.Read() != false)
                {
                    temp.Rows.Add(reader6["ACTCode"]);
                }
                reader6.Close();
            }
            for (int i = 0; i < temp.Rows.Count; i++)
            {
                SqlCommand cmd7 = new SqlCommand("select Activity.ACTDesc,ActivityCost.CurrentCost from Activity inner join ActivityCost on Activity.ACTCode = ActivityCost.ACTCode where ActivityCost.ACTCode = @ACTCode", scon);
                cmd7.Parameters.AddWithValue("@ACTCode", temp.Rows[i]["ACTCode"]);
                SqlDataReader reader7 = cmd7.ExecuteReader();
                while (reader7.Read() != false)
                {
                    showTable.Rows.Add(reader7["ACTDesc"].ToString(), reader7["CurrentCost"].ToString());
                }
                reader7.Close();
            }

            scon.Close();
            return showTable;

        }
        

        protected DataTable SortingDataTable(string CID, string caseNumber)
        {
            DataTable tempPSNumber = new DataTable();
            DataTable temp = new DataTable();
            DataTable showTable = new DataTable();

            tempPSNumber.Columns.Add("PSNumber");
            temp.Columns.Add("ACTCode");
            temp.Columns.Add("ACTDESC");
            showTable.Columns.Add("Activity");
            showTable.Columns.Add("Money",typeof(System.Double));

            double totalMoney = 0;

            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);
            scon.Open();
            SqlCommand cmd5 = new SqlCommand("select PSNumber from ServiceProvisionVsPerson where CID = @CID and CaseNo = @CaseNo", scon);
            cmd5.Parameters.AddWithValue("@CID", CID);
            cmd5.Parameters.AddWithValue("@CaseNo", caseNumber);
            SqlDataReader reader5 = cmd5.ExecuteReader();
            while (reader5.Read() != false)
            {
                tempPSNumber.Rows.Add(reader5["PSNumber"]);
            }
            reader5.Close();
            for (int i = 0; i < tempPSNumber.Rows.Count; i++)
            {
                //ตอนนี้ที่แสดงยังเป็น ActivityCode อยู่ต้องแก้
                SqlCommand cmd6 = new SqlCommand("select ServiceProvisionDetails.ACTCode from ServiceProvision inner join ServiceProvisionDetails on ServiceProvision.PSNumber = ServiceProvisionDetails.PSNumber where ServiceProvision.PSNumber = @PSNumber", scon);
                cmd6.Parameters.AddWithValue("@PSNumber", tempPSNumber.Rows[i]["PSNumber"]);
                SqlDataReader reader6 = cmd6.ExecuteReader();
                while (reader6.Read() != false)
                {
                    temp.Rows.Add(reader6["ACTCode"]);
                }
                reader6.Close();
            }

            for (int i = 0; i < temp.Rows.Count; i++)
            {
                SqlCommand cmd7 = new SqlCommand("select Activity.ACTDesc,ActivityCost.CurrentCost from Activity inner join ActivityCost on Activity.ACTCode = ActivityCost.ACTCode where ActivityCost.ACTCode = @ACTCode", scon);
                cmd7.Parameters.AddWithValue("@ACTCode", temp.Rows[i]["ACTCode"]);
                SqlDataReader reader7 = cmd7.ExecuteReader();
                while (reader7.Read() != false)
                {
                    showTable.Rows.Add(reader7["ACTDesc"].ToString(), reader7["CurrentCost"].ToString());
                }
                reader7.Close();
            }

            //Calculate Total Money
            for (int i = 0; i < showTable.Rows.Count; i++)
            {
                double tempMoney = Convert.ToDouble(showTable.Rows[i]["Money"]);
                totalMoney = totalMoney + tempMoney;
            }

            showTable.Rows.Add("เงินรวม", totalMoney);
            scon.Close();
            return showTable;
        }

        private void MessageBox(string msg)
        {
            Label lbl = new Label();
            lbl.Text = "<script language='javascript'>" + Environment.NewLine + "window.alert('" + msg + "')</script>";
            Page.Controls.Add(lbl);
        }


        protected void Button1_Click(object sender, EventArgs e)
        {
            string CID = Request.QueryString["IDStudent"];
            Response.Redirect("Report2.aspx?IDStudent=" + CID);
        }
    }
}